package com.sitc.street_lab_2013.helpers;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

//test colin OK?
public class DBHelper extends SQLiteOpenHelper {

	public static final String DB_NAME = "Quiz.db";
	public static final int DB_VERSION = 1;
	
	public static final String TABLE_LOCATIONS = "Locations";
	public static final String LOC_ID = "locId";
	public static final String LOC_QUESTION = "locQuestion";
	public static final String LOC_NAME = "locName";
	public static final String LOC_IMAGE = "locImage";
	public static final String LOC_LAT = "locLat";
	public static final String LOC_LONG = "locLong";
	public static final String LOC_ISANSWERED = "locIsAnswered";
	
	public static final String TABLE_ANSWERS = "Answers";
	public static final String ANS_QID = "ansQuestionId";
	public static final String ANS_ID = "ansId";
	public static final String ANS_TEXT = "ansText";
	public static final String ANS_ISCORRECT = "ansIsCorrect";
	public static final String ANS_MOREINFO = "ansMoreInfo";

	public DBHelper(Context c)
	{
		super (c, DB_NAME, null, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//create all tables and related constraints
		db.execSQL("create table " + TABLE_LOCATIONS + "(" 
					+ LOC_ID + " integer primary key, " 
					+ LOC_QUESTION + " text not null, "
					+ LOC_NAME + " text not null, "
					+ LOC_IMAGE + " text not null, "
					+ LOC_LAT + " real not null, "
					+ LOC_LONG + " real not null, "
					+ LOC_ISANSWERED + " integer not null "
					+ ");");
		
		db.execSQL("create table " + TABLE_ANSWERS + "(" 
				+ ANS_ID + " integer primary key, " 
				+ ANS_QID + " integer not null, "
				+ ANS_TEXT + " text not null, "
				+ ANS_ISCORRECT + " integer not null, "
				+ ANS_MOREINFO + " text not null, "
				+ "foreign key (" + ANS_QID + ") references " + TABLE_LOCATIONS + "(" + LOC_ID + ")"
				+ ");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldV, int newV) {
		Log.w(DBHelper.class.getName(), "Upgrading database from v: " + oldV
				+ " to v: " + newV);
		Log.w(DBHelper.class.getName(), "All old data will be deleted");
		
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOCATIONS + ";");
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ANSWERS + ";");
		onCreate(db);
		
	}
}
